package com.sq.partner.manager.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;

import com.sq.partner.manager.common.Constants;
import com.sq.partner.manager.dao.ITradNoticeMtDao;
import com.sq.partner.manager.mybatis.model.GatewayUser;
import com.sq.partner.manager.mybatis.model.TradNotice;
import com.sq.partner.manager.util.DBUtil;
import com.sq.partner.manager.util.LogUtil;
import com.sq.partner.manager.util.Page;
import com.sq.partner.manager.util.SqlUtil;
import com.sq.partner.manager.util.StrUtil;
/**
 * 
 * @author zzguo
 *
 */
@Repository
public class ITradNoticeMtDaoImpl extends BaseDaoImpl implements ITradNoticeMtDao {
	private static Logger logger = LogUtil.getLog(ITradNoticeMtDaoImpl.class);
	@Override
	public Page<TradNotice> queryInfoByCriteria(Page<TradNotice> page,
			GatewayUser user) throws Exception {
		Connection conn = null;
		PreparedStatement pstmt = null;
		PreparedStatement pstmt1 = null;
		ResultSet rs = null;
		try {
			String deptStr = user.getDepartmentStr();
			if(StrUtil.isNotEmpty(deptStr)){
				StringBuilder sql = new StringBuilder();
				//总公司处理
				if(deptStr.contains(Constants.HQ_BRANCH_CODE)){
					sql.append("SELECT DISTINCT T.ID,T.TITLE,T.CONTENT,T.NOTICE_TYPE,T4.CODE_NAME AS NOTICE_TYPE_DSCP,T.PRI,"
							+ "T3.CODE_NAME AS PRI_DSCP,T.STATUS,T2.CODE_NAME AS STATUS_DSCP,TO_CHAR(T.START_TIME, 'yyyy-mm-dd hh24:mi') AS START_TIME,"
							+ "TO_CHAR(T.END_TIME, 'yyyy-mm-dd hh24:mi') AS END_TIME, NVL(T.UPDATE_TIME, T.CREATE_TIME) AS ORDERBYCOLUMN FROM T_TRAD_NOTICE T INNER JOIN T_GATEWAY_DICT T2"
							+ " ON T2.CODE = TO_CHAR(T.STATUS) AND T2.CODE_TYPE = 'NOTICE_STATUS' INNER JOIN T_GATEWAY_DICT T3"
							+ " ON T3.CODE = TO_CHAR(T.PRI) AND T3.CODE_TYPE = 'NOTICE_PRI' INNER JOIN T_GATEWAY_DICT T4"
							+ " ON T4.CODE = T.NOTICE_TYPE AND T4.CODE_TYPE = 'NOTICE_TYPE' WHERE 1 = 1 ");
				}else{
					sql.append("SELECT DISTINCT T.ID,T.TITLE,T.CONTENT,T.NOTICE_TYPE,T4.CODE_NAME AS NOTICE_TYPE_DSCP,T.PRI,"
							+ "T3.CODE_NAME AS PRI_DSCP,T.STATUS,T2.CODE_NAME AS STATUS_DSCP,TO_CHAR(T.START_TIME, 'yyyy-mm-dd hh24:mi') AS START_TIME,"
							+ "TO_CHAR(T.END_TIME, 'yyyy-mm-dd hh24:mi') AS END_TIME,NVL(T.UPDATE_TIME, T.CREATE_TIME) AS ORDERBYCOLUMN FROM T_TRAD_NOTICE T INNER JOIN T_TRAD_NOTICE_PARTNER T1"
							+ " ON T.ID = T1.NOTICE_ID INNER JOIN T_GATEWAY_DICT T2 ON T2.CODE = TO_CHAR(T.STATUS)"
							+ " AND T2.CODE_TYPE = 'NOTICE_STATUS' INNER JOIN T_GATEWAY_DICT T3 ON T3.CODE = TO_CHAR(T.PRI)"
							+ " AND T3.CODE_TYPE = 'NOTICE_PRI' INNER JOIN T_GATEWAY_DICT T4 ON T4.CODE = T.NOTICE_TYPE"
							+ " AND T4.CODE_TYPE = 'NOTICE_TYPE' WHERE 1 = 1 AND T1.DEPT_NO IN ("+deptStr+")");
				}
			List<String> params = new ArrayList<String>();
			if (StrUtil.isNotEmpty(page.getType().getTitle())) {
				String title = page.getType().getTitle();
				String titleStr = "%" + title + "%";
				sql.append(" AND  T.TITLE LIKE ? ");
				params.add(titleStr);
			}
			if (StrUtil.isNotEmpty(page.getType().getNoticeTimeParam())){
				String noticeTimeParam = page.getType().getNoticeTimeParam();
				sql.append(" AND T.START_TIME < TO_DATE(?, 'YYYY-MM-DD')+1 AND T.END_TIME > TO_DATE(?, 'YYYY-MM-DD')");
				params.add(noticeTimeParam);
				params.add(noticeTimeParam);
			}
			if (null!=page.getType().getStatus()) {
				String status = page.getType().getStatus().toString();
				sql.append(" AND T.STATUS = ? ");
				params.add(status);
			}
			if (StrUtil.isNotEmpty(page.getType().getNoticeType())){
				String noticeType = page.getType().getNoticeType();
				sql.append(" AND  T.NOTICE_TYPE = ? ");
				params.add(noticeType);
			}
			sql.append(" ORDER BY ORDERBYCOLUMN DESC ");
			conn = this.getConnection();
			Long total = 0L;
			logger.debug("sql+++:"+sql.toString());
			pstmt = conn.prepareStatement("select count(*) from ("+ sql.toString() + ")");
			for (int i = 0; i < params.size(); i++) {
				pstmt.setObject(i + 1, params.get(i));
			}
			rs = pstmt.executeQuery();
			if (rs.next()) {
				total = rs.getLong(1);
			}
			page.setTotal(total);

			SqlUtil sqlUtil = new SqlUtil();

			String sqlPage = sqlUtil.buildPageSqlForOracle(sql.toString(),page).toString();

			pstmt1 = conn.prepareStatement(sqlPage);
			for (int i = 0; i < params.size(); i++) {
				pstmt1.setObject(i + 1, params.get(i));
			}
			rs = pstmt1.executeQuery();
			List transList = DBUtil.transRs2List(rs);
			page.setData(transList);
			}
		}finally{
			close(pstmt);
			close(pstmt1);
			close(rs);
			close(conn);
		}
		return  page;
	}

	@Override
	public TradNotice queryInfoByTradNoticeId(String Id) throws Exception {
		return (TradNotice) this.query("queryTradNoticeByIdForScan", Id);
	}

	@Override
	public boolean delTradNoticeById(String Id) throws Exception {
		return this.delete("delTradNoticeById", Id);
	}

	@Override
	public String addTradNoticeInfo(TradNotice tradNotice) throws Exception {
		this.addAndReturnPrimaryKey("insertTradNotice", tradNotice);
		return tradNotice.getId();
	}
}
